﻿// =================================================================== 
// 项目说明
//====================================================================
// 
// 文件： SYS_MENU.cs
// 项目名称：

// 创建时间：2013/3/19
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLib.Model;
using CommonLib.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  .
    /// </summary>
    public partial class SYS_MENUDAL : ISYS_MENUDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取SYS_MENU表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的


        private const string QueryDataString = "";

        public DataTable GetMasterLevelMenu(string user_id, string sub_system)
        {
            string sqlStr = " Select a.*,b.READONLY  From Sys_Menu a,( "
                          + "  Select Substring(a.inner_no,1,2) n_inner_no ,min(d.readonly) readonly From Sys_Menu a,"
                          + " sys_operator_role b,sys_dept_roles c,sys_role_function d Where  "
                          + " b.master_id=c.master_id and c.role_id=d.role_id and sub_system=" + sub_system
                          + " and b.user_id='" + user_id + "' and a.menu_no=d.menu_no "
                          + " GROUP BY Substring(a.inner_no,1,2)) b Where a.inner_no=b.n_inner_no and a.sub_system=" + sub_system
                          + " order by a.inner_no ";
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
        }


        public DataTable GetjuniorLevelMenu(string user_id, string menu_no)
        {
            string inner_no = "";
            string sub_system = "";
            string sqlStr = "Select INNER_NO,SUB_SYSTEM From sys_menu Where menu_no='" + menu_no + "'";
            DataTable dt = SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
            inner_no = dt.Rows[0]["inner_no"].ToString();
            sub_system = dt.Rows[0]["sub_system"].ToString();
            int k = inner_no.Length;
            int i = k + 2;
            sqlStr = "   Select a.*,b.READONLY  From Sys_Menu a,( "
                          + "  Select Substring(a.inner_no,1," + i + ") n_inner_no ,min(d.readonly) readonly From "
                          + " Sys_Menu a,sys_operator_role b,sys_dept_roles c,sys_role_function d Where "
                          + " b.user_id='" + user_id + "' and b.master_id=c.master_id and c.role_id=d.role_id  "
                          + "  and a.menu_no=d.menu_no  and a.sub_system='" + sub_system + "' and Substring(a.inner_no,1," + k + ")='" + inner_no + "' GROUP BY "
                          + " Substring(a.inner_no,1," + i + ")) b Where a.inner_no=b.n_inner_no and a.parent_menu_no='" + menu_no + "'"
                          + " order by a.inner_no ";
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];

            //Substring(a.inner_no," + i + ")='" + inner_no + "'
        }


        public DataTable GetRoleSelectMenu(string sub_system, string role_id)
        {
            string sqlStr = " select * from sys_menu where sub_system=" + int.Parse(sub_system)
                            + "and menu_no not in (select menu_no  from sys_role_function where role_id='" + role_id + "') ";
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
        }

        public DataTable GetAllMenu()
        {
            string sqlStr = "Select MENU_NO,MENU_NAME,FUNCTION_ID,IS_LEAF,PARENT_MENU_NO,SUB_SYSTEM,INNER_NO,0 AS READONLY,NULL AS ROLE_ID "
                + " ,(select u.system_name from sys_subsystem u where u.sub_system=sys_menu.SUB_SYSTEM) SYSTEM_NAME from sys_menu ";
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
        }

        public DataTable GetAllMenu2(string where)
        {
            string sqlStr = "Select MENU_NO,MENU_NAME,FUNCTION_ID,IS_LEAF,PARENT_MENU_NO,SUB_SYSTEM,INNER_NO,0 AS READONLY,NULL AS ROLE_ID "
                + " ,(select u.system_name from sys_subsystem u where u.sub_system=sys_menu.SUB_SYSTEM) SYSTEM_NAME from sys_menu ";
            if (where != "")
            {
                sqlStr += " where " + where;
            }
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
        }
    }
}